package com.tangxz.generate_code.util;

import com.tangxz.generate_code.pojo.ColumnVo;
import com.tangxz.generate_code.pojo.DataBaseVo;
import com.tangxz.generate_code.pojo.TableVo;
import com.tangxz.generate_code.pojo.TemplateVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;

import java.security.SecureRandom;
import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Slf4j
public class DataBaseUtil {
    /**
     * 1、得到一个地址下全部的数据库
     * 2、得到一个数据库的全部表
     * 3、得到一个表的tableVo
     * 1、连接数据库并返回连接
     * 2、得到数据库的全部表
     * 3、得到表的全部字段
     */

    /**
     * 得到一个连接下全部的数据库名
     *
     * @param databaseVo 其中的url不需要数据库名，数据库名字段也可以不要
     * @return
     */
    public static List<String> getAllDatabaseName(DataBaseVo databaseVo) {
        List<String> databaseNameList = new ArrayList<>();
        Connection connection = null;
        try {
            Class.forName(databaseVo.getDiver_name());
            connection = DriverManager.getConnection(databaseVo.getDatabase_url(), databaseVo.getDatabase_username(), databaseVo.getDatabase_password());
            Statement statement = connection.createStatement();
//            得到全部的数据库
            ResultSet rs = statement.executeQuery("show databases");
            //得到demo表的全部字段：SHOW FULL COLUMNS FROM demo;
            rs.first();
            for (int i = 0; i < rs.getRow(); i++) {
                System.out.println(rs.getString(1));
                databaseNameList.add(rs.getString(1));
                rs.next();
            }
        } catch (ClassNotFoundException | SQLException classNotFoundException) {
            classNotFoundException.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.gc();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return databaseNameList;
    }

    /**
     * 生成文件，得到该表全部需要的信息，这个databaseVo中必须包含数据库名！
     *
     * @param databaseVo
     * @return
     */
    public static List<TableVo> getOneDatabaseTable(DataBaseVo databaseVo) {
//        DatabaseVo databaseVo = new DatabaseVo("tangxz/database");
        Connection connection = null;
        List<TableVo> tableVoList = new ArrayList<>();
        try {
            Class.forName(databaseVo.getDiver_name());
            connection = DriverManager.getConnection(databaseVo.getDatabase_url(), databaseVo.getDatabase_username(), databaseVo.getDatabase_password());
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
//                System.out.println("表名：" + rs.getString("TABLE_NAME") + "<br/>" +
//                        "表类型：" + rs.getString("TABLE_TYPE") + "<br/>" +
//                        "表所属数据库：" + rs.getString("TABLE_CAT") + "<br/>" +
//                        "表所属用户名：" + rs.getString("TABLE_SCHEM") + "<br/>" +
//                        "表备注：" + rs.getString("REMARKS") + "<br/>");
                TableVo tableVo = new TableVo();
                String tableName = rs.getString("TABLE_NAME");
                String excludePrefix = FieldUtil.toExcludePrefix(tableName);
                tableVo.setTableName(tableName);
                tableVo.setPrimaryKeyPolicy("uuid");
                tableVo.setEntityName(excludePrefix);//类名全大写
                tableVo.setEntityPackage(excludePrefix.toLowerCase());//包名小写
                tableVo.setFtlDescription(rs.getString("REMARKS"));
                tableVoList.add(tableVo);
            }
            for (TableVo tableVo : tableVoList) {
                System.out.println(tableVo.toString());
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.gc();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return tableVoList;
    }

    /**
     * 批量得到多个表的tableInfos
     *
     * @param tableVos
     * @param templateVo
     * @param databaseVo
     * @return
     */
    public static List<Map<String, Object>> getTableInfos(TableVo[] tableVos, TemplateVo templateVo, DataBaseVo databaseVo) {
        List<Map<String, Object>> tableInfos = new ArrayList<>();
        Connection connection = null;
        try {
            Class.forName(databaseVo.getDiver_name());
            connection = DriverManager.getConnection(databaseVo.getDatabase_url(), databaseVo.getDatabase_username(), databaseVo.getDatabase_password());
            for (TableVo tableVo : tableVos) {
                Map<String, Object> tableInfo = new HashMap<>();
                tableInfo.put("bussiPackage", templateVo.getBussiPackage());
                tableInfo.put("entityPackage", tableVo.getEntityPackage());
                String entityName = tableVo.getEntityName();
                entityName = entityName.substring(0, 1).toUpperCase()+entityName.substring(1);
                tableInfo.put("entityName", entityName);//首字母大写
                tableInfo.put("tableName", tableVo.getTableName());
                tableInfo.put("primaryKeyField", "id");
                tableInfo.put("tableVo", tableVo);
                //将数据表所有字段 columnVoList 存入 tableInfo
                List<ColumnVo> columnVos = DataBaseUtil.loadOneTableColumnVo(connection, databaseVo, tableVo.getTableName());
                tableInfo.put("originalColumns", columnVos);
                //找主键
                for (ColumnVo column : columnVos) {
                    if (column.getFieldName().toLowerCase().equals("id".toLowerCase())) {
                        tableInfo.put("primaryKeyPolicy", column.getFieldType());
                    }
                }
                //生成一个唯一标识uuid
                long uuid = (new SecureRandom()).nextLong() + System.currentTimeMillis();
                tableInfo.put("serialVersionUID", String.valueOf(uuid));
                tableInfos.add(tableInfo);
            }
        } catch (ClassNotFoundException | SQLException classNotFoundException) {
            log.error("--- 数据库连接失败");
            classNotFoundException.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.gc();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return tableInfos;
    }

    /**
     * 得到当前表的所有字段
     *
     * @param connection 作为参数，减少连接次数
     * @param databaseVo 数据库信息
     * @param tableName  表名
     */
    public static List<ColumnVo> loadOneTableColumnVo(Connection connection, DataBaseVo databaseVo, String tableName) {
        String sql;
        List<ColumnVo> res = new ArrayList<>();
        Statement statement = null;
        try {
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            sql = getSql(databaseVo.getDatabase_name(), tableName);
            ResultSet rs = statement.executeQuery(sql);
            rs.first();
            int var4 = rs.getRow();
            if (var4 <= 0) {
                throw new Exception("该表不存在或者表中没有字段");
            }
            while (true) {
                ColumnVo columnVo = new ColumnVo();
                columnVo.setFieldName(FieldUtil.toCamelCase(rs.getString(1).toLowerCase()));
                columnVo.setFieldDbName(rs.getString(1).toUpperCase());
                columnVo.setPrecision(isBlank(rs.getString(4)));
                columnVo.setScale(isBlank(rs.getString(5)));
                columnVo.setCharmaxLength(isBlank(rs.getString(6)));
                columnVo.setNullable(getNullAble(rs.getString(7)));
                columnVo.setFieldType(getDataStructure(rs.getString(2).toLowerCase(), columnVo.getPrecision(), columnVo.getScale()));
                columnVo.setFieldDbType(FieldUtil.toCamelCase(rs.getString(2).toLowerCase()));
//                a(columnVo);
                columnVo.setFiledComment(StringUtils.isBlank(rs.getString(3)) ? columnVo.getFieldName() : rs.getString(3));
                res.add(columnVo);
                log.debug("columnt.getFieldName() -------------" + columnVo.getFieldName());
                if (!rs.next()) {
                    log.debug("读取表成功-一共有{}个字段",res.size());
                    break;
                }
            }
        } catch (Exception throwables) {
            log.error("--- 数据表不存在");
            throwables.printStackTrace();
        } finally {
            if (statement != null) {
                System.gc();
            }
        }
        return res;
    }

    /**
     * 测试数据库的某张表是否可以存在
     *
     * @param databaseVo
     * @param tableName
     * @return
     */
    public static boolean testTableIsExist(DataBaseVo databaseVo, String tableName) throws SQLException {
        String sql;
        Connection connection = null;
        Statement statement = null;
        try {
            log.debug("数据库驱动: " + databaseVo.getDiver_name());
            Class.forName(databaseVo.getDiver_name());
            connection = DriverManager.getConnection(databaseVo.getDatabase_url(), databaseVo.getDatabase_username(), databaseVo.getDatabase_password());
            statement = connection.createStatement(1005, 1007);
            sql = getSql(databaseVo.getDatabase_name(), tableName);
            ResultSet rs = statement.executeQuery(sql);
            rs.last();
            int res = rs.getRow();
            return res > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (statement != null) {
                statement.close();
                System.gc();
            }

            if (connection != null) {
                connection.close();
                System.gc();
            }
        }
    }

    /**
     * 配置前端的一些规则
     * @param columnVo
     */
    private static void a(ColumnVo columnVo) {
        String var1 = columnVo.getFieldType();
        String sql = columnVo.getScale();
        columnVo.setClassType("inputxt");
        if ("N".equals(columnVo.getNullable())) {
            columnVo.setOptionType("*");
        }

        if (!"datetime".equals(var1) && !var1.contains("time")) {
            if ("date".equals(var1)) {
                columnVo.setClassType("easyui-datebox");
            } else if (var1.contains("int")) {
                columnVo.setOptionType("n");
            } else if ("number".equals(var1)) {
                if (StringUtils.isNotBlank(sql) && Integer.parseInt(sql) > 0) {
                    columnVo.setOptionType("d");
                }
            } else if (!"float".equals(var1) && !"double".equals(var1) && !"decimal".equals(var1)) {
                if ("numeric".equals(var1)) {
                    columnVo.setOptionType("d");
                }
            } else {
                columnVo.setOptionType("d");
            }
        } else {
            columnVo.setClassType("easyui-datetimebox");
        }

    }

    /**
     * 返回字段数据结构
     *
     * @param var0
     * @param var1
     * @param sql
     * @return
     */
    private static String getDataStructure(String var0, String var1, String sql) {
        if (var0.contains("char")) {
            var0 = "java.lang.String";
        } else if (var0.contains("int")) {
            var0 = "java.lang.Integer";
        } else if (var0.contains("float")) {
            var0 = "java.lang.Float";
        } else if (var0.contains("double")) {
            var0 = "java.lang.Double";
        } else if (var0.contains("number")) {
            if (StringUtils.isNotBlank(sql) && Integer.parseInt(sql) > 0) {
                var0 = "java.math.BigDecimal";
            } else if (StringUtils.isNotBlank(var1) && Integer.parseInt(var1) > 10) {
                var0 = "java.lang.Long";
            } else {
                var0 = "java.lang.Integer";
            }
        } else if (var0.contains("decimal")) {
            var0 = "java.math.BigDecimal";
        } else if (var0.contains("date")) {
            var0 = "java.util.Date";
        } else if (var0.contains("time")) {
            var0 = "java.util.Date";
        } else if (var0.contains("blob")) {
            var0 = "byte[]";
        } else if (var0.contains("clob")) {
            var0 = "java.sql.Clob";
        } else if (var0.contains("numeric")) {
            var0 = "java.math.BigDecimal";
        } else {
            var0 = "java.lang.Object";
        }
        return var0;
    }

    private static String getSql(String databaseName, String tableName) {
        return MessageFormat.format("select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns where table_name = {0} and table_schema = {1} order by ORDINAL_POSITION", FieldUtil.fieldValue(tableName), FieldUtil.fieldValue(databaseName));
    }

    private static String getNullAble(String var0) {
        if (!"YES".equals(var0) && !"yes".equals(var0) && !"y".equals(var0) && !"Y".equals(var0) && !"f".equals(var0)) {
            return !"NO".equals(var0) && !"N".equals(var0) && !"no".equals(var0) && !"n".equals(var0) && !"t".equals(var0) ? null : "N";
        } else {
            return "Y";
        }
    }

    public static String isBlank(String str) {
        return StringUtils.isBlank(str) ? "" : str;
    }

    private static Map<String, String> database_url_add;

    static {
        database_url_add = new HashMap<>();
        database_url_add.put("useUnicode", "true");
        database_url_add.put("characterEncoding", "UTF-8");
        database_url_add.put("serverTimezone", "Asia/Shanghai");
        database_url_add.put("useSSL", "false");
    }

    public static String getDatabaseUrl(String database_url) {
        if (database_url_add.size() > 0) {
            database_url = database_url + "?";
        }
        int i = 0;
        StringBuilder database_urlBuilder = new StringBuilder(database_url);
        for (String key : database_url_add.keySet()) {
            i++;
            database_urlBuilder.append(key).append("=").append(database_url_add.get(key)).append(i < database_url_add.size() ? "&" : "");
        }
        return database_urlBuilder.toString();
    }
}
